﻿#include"DB.h"

RecordBean::RecordBean() { //打开recordBean
	int rc = sqlite3_open_v2(dataPath, &(RecordBean::db), SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

	if (rc) {
		fprintf(stderr, "Can't open database:%s\n", sqlite3_errmsg(RecordBean::db));
		exit(-1);
	}
	else {
		const char* sql = "CREATE TABLE IF NOT EXISTS record_bean(\
			roomId INTEGER,\
			recordId INTEGER,\
			_time INTEGER,\
			type INTEGER,\
			source INTEGER,\
			queryType INTEGER,\
			recordType INTEGER,\
			currentT REAL,\
			currentW INTEGER,\
			requestT REAL,\
			requestW INTEGER,\
			optionType INTEGER\
			)";
		const char* sql2 = "CREATE TABLE IF NOT EXISTS record_bean_copy(\
			roomId INTEGER,\
			recordId INTEGER,\
			_time INTEGER,\
			type INTEGER,\
			source INTEGER,\
			queryType INTEGER,\
			recordType INTEGER,\
			currentT REAL,\
			currentW INTEGER,\
			requestT REAL,\
			requestW INTEGER,\
			optionType INTEGER\
			)";
		int result = sqlite3_prepare_v2((RecordBean::db), sql, -1, &(stmt), NULL);
		if (result == SQLITE_OK) {
			//std::cout << "create table successfully" << std::endl;
			sqlite3_step(stmt);
		}
		else {
			std::cout << "failed to create table" << std::endl;
		}
		sqlite3_finalize(stmt);

		//创立第二个表，作为副本
		result = sqlite3_prepare_v2(RecordBean::db, sql2, -1, &(stmt), NULL);
		if (result == SQLITE_OK) {
			//std::cout << "create table successfully" << std::endl;
			sqlite3_step(stmt);
		}
		else {
			std::cout << "failed to create table" << std::endl;
		}
		sqlite3_finalize(stmt);
	}

}


void RecordBean::closeDB(void) {
	sqlite3_close_v2(RecordBean::db);
}

bool RecordBean::insert(Record* rd) //插入成功返回true，失败返回false
{

	char sqlSentence[255] = {};
	sprintf_s(sqlSentence, "INSERT INTO record_bean VALUES(%d,%d,%d,%d,%d,%d,%d,%lf,%d,%lf,%d,%d)"
		, rd->getRoomId(), rd->getRecordId(), (int)rd->getTime(), (int)rd->getType()
		, (int)rd->getSource(), (int)rd->getqueryType(), (int)rd->getRecordType(),
		rd->getCurrentT(), (int)rd->getCurrentW(), rd->getRequestT(), (int)rd->getRequestW(), (int)rd->getOptionType()
	);
	int result = sqlite3_prepare_v2(RecordBean::db, sqlSentence, -1, &(stmt), NULL);

	if (result == SQLITE_OK) {
		std::cout << "insert successfully" << std::endl;
		sqlite3_step(stmt);
	}
	else {
		std::cout << "failed to insert" << std::endl;
		exit(-1);
	}
	sqlite3_finalize(stmt);

	char sqlSentence2[255] = {};
	sprintf_s(sqlSentence2, "INSERT INTO record_bean_copy VALUES(%d,%d,%d,%d,%d,%d,%d,%lf,%d,%lf,%d,%d)"
		, rd->getRoomId(), rd->getRecordId(), (int)rd->getTime(), (int)rd->getType()
		, (int)rd->getSource(), (int)rd->getqueryType(), (int)rd->getRecordType(),
		rd->getCurrentT(), (int)rd->getCurrentW(), rd->getRequestT(), (int)rd->getRequestW(), (int)rd->getOptionType()
	);
	result = sqlite3_prepare_v2(RecordBean::db, sqlSentence2, -1, &(stmt), NULL);

	if (result == SQLITE_OK) {
		std::cout << "insert successfully" << std::endl;
		sqlite3_step(stmt);
	}
	else {
		std::cout << "failed to insert" << std::endl;
		exit(-1);
	}
	sqlite3_finalize(stmt);
	return true;
}


std::vector<reqPayment*> RecordBean::query1(int roomId) // 请求账单
{
	char sqlSentence[255] = {};
	sprintf_s(sqlSentence, "SELECT recordId,_time,currentW\
		FROM record_bean\
		WHERE roomId = %d AND(recordType = 2 OR recordType = 3) ORDER BY _time", roomId);
<<<<<<< HEAD

=======
	
>>>>>>> c3e9195f1a49446cae14ce4dd3fefe59563eea69
	int result = sqlite3_prepare_v2(RecordBean::db, sqlSentence, -1, &(stmt), NULL);
	std::vector<reqPayment*> req;
	if (result == SQLITE_OK) {
		while (sqlite3_step(stmt) == SQLITE_ROW) {
			reqPayment* rp = new reqPayment();
			rp->recordId = sqlite3_column_int(stmt, 0);
			rp->time = (time_t)sqlite3_column_int(stmt, 1);
			rp->wd = (WindMood)sqlite3_column_int(stmt, 2);
			req.push_back(rp);
		}
	}
	else {
		std::cout << "failed to select" << std::endl;
	}
	sqlite3_finalize(stmt);
	return req;
}
std::vector<reqPayment*> RecordBean::query1(int roomId, time_t startTime, time_t endTime) // 请求账单
{
	char sqlSentence[255] = {};
	sprintf_s(sqlSentence, "SELECT recordId,_time,currentW\
		FROM record_bean_copy\
<<<<<<< HEAD
		WHERE roomId = %d AND(recordType = 2 OR recordType = 3) AND _time >=%d AND _time <=%d ORDER BY _time", roomId, (int)startTime, (int)endTime);
=======
		WHERE roomId = %d AND(recordType = 2 OR recordType = 3) AND _time >=%d AND _time <=%d ORDER BY _time", roomId,(int)startTime, (int)endTime);
>>>>>>> c3e9195f1a49446cae14ce4dd3fefe59563eea69

	int result = sqlite3_prepare_v2(RecordBean::db, sqlSentence, -1, &(stmt), NULL);
	std::vector<reqPayment*> req;
	if (result == SQLITE_OK) {
		while (sqlite3_step(stmt) == SQLITE_ROW) {
			reqPayment* rp = new reqPayment();
			rp->recordId = sqlite3_column_int(stmt, 0);
			rp->time = (time_t)sqlite3_column_int(stmt, 1);
			rp->wd = (WindMood)sqlite3_column_int(stmt, 2);
			req.push_back(rp);
		}
	}
	else {
		std::cout << "failed to select" << std::endl;
	}
	sqlite3_finalize(stmt);
	return req;
}

std::vector<reqDetail*> RecordBean::query2(int roomId) // 请求详单，暂定加个bool为详单的重载函数
{
	char sqlSentence[255] = {};
	sprintf_s(sqlSentence, "SELECT _time, optionType,requestT,requestW \
		FROM record_bean \
		WHERE roomId = %d AND recordType = 1 ORDER BY _time", roomId);

	int result = sqlite3_prepare_v2(RecordBean::db, sqlSentence, -1, &(stmt), NULL);
	std::vector<reqDetail*>req;
	if (result == SQLITE_OK) {
		while (sqlite3_step(stmt) == SQLITE_ROW) {
			reqDetail* re = new reqDetail();
			re->time = sqlite3_column_int(stmt, 0);
			re->ot = (OptionType)sqlite3_column_int(stmt, 1);
			if (re->ot == OptionType::CHANGET) {
				re->rt = sqlite3_column_int(stmt, 2);
				re->rw = WindMood::NONE;
			}
			else if (re->ot == OptionType::CHANGEW) {
				re->rt = 0;
				re->rw = (WindMood)sqlite3_column_int(stmt, 3);
			}
			else {
				re->rt = 0;
				re->rw = WindMood::NONE;
			}
			req.push_back(re);
		}
	}
	else {
		std::cout << "failed to select" << std::endl;
	}
	sqlite3_finalize(stmt);

	//请求详单后，把在里面的房间数据删除
	char sql2[255] = {};
	sprintf_s(sql2, "DELETE FROM record_bean WHERE roomId = %d", roomId);
	result = sqlite3_prepare_v2(RecordBean::db, sql2, -1, &(stmt), NULL);
	if (result == SQLITE_OK) {
		sqlite3_step(stmt);
	}
	else {
		std::cout << "falied to delete" << std::endl;
	}
	sqlite3_finalize(stmt);
	return req;
}

std::vector<reqStatement*> RecordBean::query3(time_t startTime, time_t endTime)   // 请求报表
{
	//共需要执行7次sql语句
	//找出当前房间
	char sql1[255] = {};
	std::vector<reqStatement*> req;
	sprintf_s(sql1, "SELECT DISTINCT(roomId)\
		FROM record_bean_copy\
		WHERE optionType = 1 AND _time >=%d AND _time <=%d ORDER BY _time", (int)startTime, (int)endTime);

	std::vector<int>roomList;
	int result = sqlite3_prepare_v2(RecordBean::db, sql1, -1, &(stmt), NULL);
<<<<<<< HEAD
	if (result == SQLITE_OK) {
=======
	if(result == SQLITE_OK) {
>>>>>>> c3e9195f1a49446cae14ce4dd3fefe59563eea69
		while (sqlite3_step(stmt) == SQLITE_ROW) {
			int roomId = sqlite3_column_int(stmt, 0);
			roomList.push_back(roomId);
		}
	}
	else {
		std::cout << "failed to find room" << std::endl;
		exit(-1);
	}
	sqlite3_finalize(stmt);
	//统计所有房间的关机次数
	int countOFF = 0;
	char sql2[255] = {};
	sprintf_s(sql2, "SELECT count(recordId) \
		FROM record_bean_copy\
		WHERE optionType = 2 AND _time >=%d AND _time <=%d ORDER BY _time", (int)startTime, (int)endTime);
	result = sqlite3_prepare_v2(RecordBean::db, sql2, -1, &(stmt), NULL);
	if (result == SQLITE_OK) {
		while (sqlite3_step(stmt) == SQLITE_ROW) {
			countOFF = sqlite3_column_int(stmt, 0);
		}
	}
	else {
		std::cout << "failed to find room" << std::endl;
		exit(-1);
	}
	sqlite3_finalize(stmt);


	//针对单个房间进行查询
	for (int i = 0; i < (int)roomList.size(); i++) {
		reqStatement* rp = new reqStatement();
		rp->ACUserCount = countOFF;
		rp->roomId = roomList[i];
		//查询最常用目标温度
		char sql3[255] = {};
		sprintf_s(sql3, "SELECT requestT, _time,optionType\
			FROM record_bean_copy\
			WHERE roomId = %d AND (optionType = 3 OR optionType = 2) AND _time >=%d AND _time <=%d ORDER BY _time", roomList[i], (int)startTime, (int)endTime);

		result = sqlite3_prepare_v2(RecordBean::db, sql3, -1, &(stmt), NULL);
		std::vector<int>requestT; //储存读入的requestT
		std::vector<int>_time;//储存读入的_time
		std::vector<int>optionType;
		std::map<int, int>commonT;
		int MAXTime = 0;
		if (result == SQLITE_OK) {
			while (sqlite3_step(stmt) == SQLITE_ROW) {
				requestT.push_back(sqlite3_column_int(stmt, 0));
				_time.push_back(sqlite3_column_int(stmt, 1));
				optionType.push_back(sqlite3_column_int(stmt, 2));
				int temp = sqlite3_column_int(stmt, 0);
				commonT[temp] = 0;
			}
			if ((int)requestT.size() == 0) { //如果没有则为默认温度
				rp->roomCommonTemp = 26;
			}
			else {
				for (int i = 0; i < (int)optionType.size(); i++) {
					if (i < (int)optionType.size() - 1 && optionType[i] == 3) {//3调温
						commonT[requestT[i]] += (_time[i + 1] - _time[i]);
					}
					else if (i == (int)optionType.size() - 1 && optionType[i] == 3) { //如果最后一个是调温
						commonT[requestT[i]] += (int)(endTime - _time[i]);
					}
				}//得到了所有温度对应的时间
				for (int i = 0; i < (int)requestT.size(); i++) {
					if (MAXTime < commonT[requestT[i]]) {
						MAXTime = commonT[requestT[i]];
						rp->roomCommonTemp = requestT[i];
					}
				}
			}
		}
		else {
			std::cout << "failed" << std::endl;
			exit(-1);
		}
		sqlite3_finalize(stmt);
		//以上的到了最常用温度

		//查询最常用风速
		//查询最常用目标温度
		char sql4[255] = {};
		sprintf_s(sql4, "SELECT requestW, _time, optionType\
			FROM record_bean_copy\
			WHERE roomId = %d AND (optionType = 4 OR optionType = 2) AND _time >=%d AND _time <=%d ORDER BY _time", roomList[i], (int)startTime, (int)endTime);

		result = sqlite3_prepare_v2(RecordBean::db, sql4, -1, &(stmt), NULL);
		std::vector<int>requestW; //储存读入的requestT
		std::vector<time_t>_time2;//储存读入的_time
		std::vector<int>optionType2;
		std::map<int, int>commonW;
		MAXTime = 0;
		if (result == SQLITE_OK) {
			while (sqlite3_step(stmt) == SQLITE_ROW) {
				requestW.push_back(sqlite3_column_int(stmt, 0));
				_time2.push_back(sqlite3_column_int(stmt, 1));
				optionType2.push_back(sqlite3_column_int(stmt, 2));
				commonW[sqlite3_column_int(stmt, 0)] = 0;
			}
			if ((int)requestW.size() == 0) { //如果没有则为默认风速
				rp->roomCoomonWind = (WindMood)0; //没有调风
			}
			else {
				for (int i = 0; i < (int)optionType2.size(); i++) {
					if (i < (int)optionType2.size() - 1 && optionType2[i] == 4) {//4为调风
						commonW[requestW[i]] += (int)(_time2[i + 1] - _time2[i]);
					}
					else if (i == (int)optionType2.size() - 1 && optionType2[i] == 4) { //如果最后一个是调风
						commonW[requestW[i]] += (int)(endTime - _time2[i]);
					}
				}//得到了所有风速对应的时间
				for (int i = 0; i < (int)requestW.size(); i++) {
					if (MAXTime < commonW[requestW[i]]) {
						MAXTime = commonW[requestW[i]];
						rp->roomCoomonWind = (WindMood)requestW[i];
					}
				}
			}
		}
		else {
			std::cout << "failed" << std::endl;
			exit(-1);
		}
		sqlite3_finalize(stmt);
		//以上得到了最常用风速

		//该房间达到目标温度的次数
		char sql5[255] = {};
		sprintf_s(sql5, "SELECT count(recordId)\
			FROM record_bean_copy\
			WHERE roomId = %d AND recordType = 3 AND _time >= %d AND _time <= %d ORDER BY _time", roomList[i], (int)startTime, (int)endTime);
		result = sqlite3_prepare_v2(RecordBean::db, sql5, -1, &stmt, NULL);
		if (result == SQLITE_OK) {
<<<<<<< HEAD

=======
			
>>>>>>> c3e9195f1a49446cae14ce4dd3fefe59563eea69
			while (sqlite3_step(stmt) == SQLITE_ROW) {
				rp->roomReachTempCount = sqlite3_column_int(stmt, 0);
			}
		}
		else {
			std::cout << "failed" << std::endl;
			exit(-1);
		}
		//得到该房间达到目标温度的次数
		//计算被调度的次数
		char sql6[255] = {};
		sprintf_s(sql6, "SELECT count(recordId)\
			FROM record_bean_copy\
			WHERE roomId = %d AND recordType = 2 AND _time >= %d AND _time <= %d ORDER BY _time", roomList[i], (int)startTime, (int)endTime);
		result = sqlite3_prepare_v2(RecordBean::db, sql6, -1, &stmt, NULL);
		if (result == SQLITE_OK) {
<<<<<<< HEAD

=======
			
>>>>>>> c3e9195f1a49446cae14ce4dd3fefe59563eea69
			while (sqlite3_step(stmt) == SQLITE_ROW) {
				rp->roomServiceCount = sqlite3_column_int(stmt, 0);
			}
		}
		else {
			std::cout << "failed" << std::endl;
			exit(-1);
		}
		//获得被调度的次数
		//计算详单记录数
		char sql7[255] = {};
		sprintf_s(sql7, "SELECT count(recordId)\
			FROM record_bean_copy\
			WHERE roomId = %d AND recordType = 1 AND _time >= %d AND _time <= %d ORDER BY _time", roomList[i], (int)startTime, (int)endTime);
		result = sqlite3_prepare_v2(RecordBean::db, sql7, -1, &stmt, NULL);
		if (result == SQLITE_OK) {
<<<<<<< HEAD

=======
			
>>>>>>> c3e9195f1a49446cae14ce4dd3fefe59563eea69
			while (sqlite3_step(stmt) == SQLITE_ROW) {
				rp->roomRecordCount = sqlite3_column_int(stmt, 0);
			}
		}
		else {
			std::cout << "failed" << std::endl;
			exit(-1);
		}
		//获得详单记录数

		//获取费用
		std::vector<reqPayment*> tmpreq = query1(roomList[i], startTime, endTime);
		PaymentList* tmp = new PaymentList(roomList[i], -1, tmpreq);
		rp->totalfee = tmp->gettotalFee();
		delete tmp;
		req.push_back(rp);
	}
	return req;
}

